package com.csun.dsms.service.impl;


import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.csun.dsms.comment.core.support.BaseService;
import com.csun.dsms.comment.core.utils.wrapper.WrapMapper;
import com.csun.dsms.comment.core.utils.wrapper.Wrapper;
import com.csun.dsms.mapper.JyDataMapper;
import com.csun.dsms.mapper.JyProblemManagementRecordMapper;
import com.csun.dsms.model.education.JyProblemManagement;
import com.csun.dsms.model.education.JyProblemManagementRecord;
import com.csun.dsms.model.education.JyQueryDto;
import com.csun.dsms.service.JyDataService;
import com.csun.dsms.service.JyReminderDataService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.text.NumberFormat;
import java.util.*;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.stream.Collectors;

@Slf4j
@Service
public class JyDataServiceImpl extends BaseService<JyProblemManagement>
        implements JyDataService {

    @Resource
    private JyDataMapper jyDataMapper;

    @Resource
    private JyReminderDataService jyReminderDataService;

    @Resource
    private JyProblemManagementRecordMapper jyProblemManagementRecordMapper;

    @Override
    public PageInfo queryListWithPage(JyQueryDto jyQueryDto) {
        PageHelper.startPage(jyQueryDto.getPageNum(), jyQueryDto.getPageSize());
        return new PageInfo<>(jyDataMapper.queryListWithPage(jyQueryDto));
    }


    @Override
    public void downExcelData(HttpServletResponse response) {
        try {
            List<String> row = CollUtil.newArrayList("清单查摆问题表现", "问题大类", "突出问题类型", "整改措施", "责任单位", "牵头领导", "完成时限",
                    "自查自纠发现问题数", "已销号", "整治中", "查处违纪违法案件数", "组织处理人数", "纪律处分人数",
                    "印发通报编号1", "通报涉及本单位问题数1", "印发通报编号2", "通报涉及本单位问题数2", "印发通报编号3", "通报涉及本单位问题数3", "印发通报编号4", "通报涉及本单位问题数4", "印发通报编号5", "通报涉及本单位问题数5",
                    "发出执纪执法建议（文书）数", "建立制度数",
                    "制度名称1", "制度名称2", "制度名称3", "制度名称4", "制度名称5",
                    "是否属于“十大攻坚”", "分管领导", "其他情况", "区划名称");
            List<List<String>> rows = CollUtil.newArrayList(row, CollUtil.newArrayList());
            ExcelWriter writer = ExcelUtil.getWriter(true);
            writer.write(rows, true);

            //设置宽度20
            for (int i = 0; i < row.size(); i++) {
                writer.setColumnWidth(i, 20);
            }

            CellRangeAddressList region = new CellRangeAddressList(0, 100, 0, 0);
            writer.addSelect(region,"问题大类1","问题大类2","问题大类3","问题大类4");


            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=ExcelData.xlsx");
            ServletOutputStream out = response.getOutputStream();

            writer.flush(out, true);
            writer.close();
            IoUtil.close(out);
        } catch (Exception e) {
            log.error("下载模板数据报错", e);
        }
    }

    @Override
    public Wrapper<JyProblemManagement> queryProblemManagementTotal(JSONObject reqJson) {
        try {

            JyProblemManagement problemManagementTotal = jyDataMapper.queryProblemManagementTotal(reqJson);

            return WrapMapper.wrap(Wrapper.SUCCESS_CODE, Wrapper.SUCCESS_MESSAGE, problemManagementTotal);
        } catch (Exception e) {
            log.error("查询数据报错", e);
            return WrapMapper.wrap(Wrapper.ERROR_CODE, e.getMessage());
        }
    }

    @Override
    public Wrapper<PageInfo<JyProblemManagement>> queryProblemManagementPage(JSONObject reqJson) {
        try {
            Integer pageNum = reqJson.getInteger("pageNum");
            Integer pageSize = reqJson.getInteger("pageSize");
            if (pageNum == null || pageSize == null) {
                return WrapMapper.error("页码和每页数量不能为空");
            }
            PageHelper.startPage(pageNum, pageSize);

            List<JyProblemManagement> problemManagementList = jyDataMapper.queryProblemManagementPage(reqJson);
            PageInfo<JyProblemManagement> pageInfo = new PageInfo<>(problemManagementList);

            return WrapMapper.wrap(Wrapper.SUCCESS_CODE, Wrapper.SUCCESS_MESSAGE, pageInfo);
        } catch (Exception e) {
            log.error("查询数据报错", e);
            return WrapMapper.wrap(Wrapper.ERROR_CODE, e.getMessage());
        }
    }

    @Override
    public Wrapper<Object> importData(MultipartFile file) {
        try {
            ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
            List<Future<?>> futures = new ArrayList<>();

            ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
            List<Map<String, Object>> ExcelList = reader.readAll();
            if (CollectionUtil.isEmpty(ExcelList)) {
                return WrapMapper.wrap(Wrapper.ERROR_CODE, "Excel不能为空！");
            }
            for (Map<String, Object> excelMap : ExcelList) {
                Future<?> future = executorService.submit(() -> {
                    JyProblemManagement jyProblemManagement = new JyProblemManagement();
                    jyProblemManagement.setProminentProblem(Convert.toStr(excelMap.get("清单查摆问题表现")));
                    jyProblemManagement.setBigTypeName(Convert.toStr(excelMap.get("问题大类")));
                    jyProblemManagement.setProblemType(Convert.toStr(excelMap.get("突出问题类型")));
                    jyProblemManagement.setRectificationMeasures(Convert.toStr(excelMap.get("整改措施")));
                    jyProblemManagement.setDepartment(Convert.toStr(excelMap.get("责任单位")));
                    jyProblemManagement.setResponsibleLeader(Convert.toStr(excelMap.get("牵头领导")));
                    jyProblemManagement.setProblemsFoundCount(Convert.toInt(excelMap.get("自查自纠发现问题数")));
                    jyProblemManagement.setClosedCount(Convert.toInt(excelMap.get("已销号")));
                    jyProblemManagement.setInProgressCount(Convert.toInt(excelMap.get("整治中")));
                    jyProblemManagement.setDisciplinaryCases(Convert.toInt(excelMap.get("查处违纪违法案件数")));
                    jyProblemManagement.setPunishedPersons(Convert.toInt(excelMap.get("组织处理人数")));
                    jyProblemManagement.setDisciplinaryPunishedPersons(Convert.toInt(excelMap.get("纪律处分人数")));
                    jyProblemManagement.setRecommendationLetters(Convert.toInt(excelMap.get("发出执纪执法建议（文书）数")));
                    jyProblemManagement.setEstablishedSystems(Convert.toInt(excelMap.get("建立制度数")));
                    jyProblemManagement.setIsTopTen(Convert.toInt(excelMap.get("是否属于“十大攻坚")));
                    jyProblemManagement.setSupervisingLeader(Convert.toStr(excelMap.get("分管领导")));
                    jyProblemManagement.setRemark(Convert.toStr(excelMap.get("其他情况")));
                    jyProblemManagement.setAreaName(Convert.toStr(excelMap.get("区划名称")));
                    jyProblemManagement.setCreatedTime(DateUtil.date());
                    String completionDeadlineStr = Convert.toStr(excelMap.get("完成时限"));
                    if (completionDeadlineStr != null && !completionDeadlineStr.isEmpty()) {
                        // 将字符串转换为 Date 对象
                        java.util.Date completionDeadlineDate = DateUtil.parse(completionDeadlineStr);
                        // 格式化日期
                        String formattedDate = DateUtil.format(completionDeadlineDate, "yyyy-MM-dd");
                        jyProblemManagement.setCompletionDeadline(formattedDate);
                    }

                    List<JSONObject> yftbbhJsonList = CollectionUtil.newArrayList();
                    Object yftbbh1 = excelMap.get("印发通报编号1");
                    Object tbsjbdwwts1 = excelMap.get("通报涉及本单位问题数1");
                    if (StrUtil.isNotBlank(Convert.toStr(yftbbh1)) && StrUtil.isNotBlank(Convert.toStr(tbsjbdwwts1))) {
                        JSONObject yftbbhJson = new JSONObject();
                        yftbbhJson.put("no", Convert.toStr(yftbbh1));
                        yftbbhJson.put("value", Convert.toInt(tbsjbdwwts1));
                        yftbbhJsonList.add(yftbbhJson);
                    }
                    Object yftbbh2 = excelMap.get("印发通报编号2");
                    Object tbsjbdwwts2 = excelMap.get("通报涉及本单位问题数2");
                    if (StrUtil.isNotBlank(Convert.toStr(yftbbh2)) && StrUtil.isNotBlank(Convert.toStr(tbsjbdwwts2))) {
                        JSONObject yftbbhJson = new JSONObject();
                        yftbbhJson.put("no", Convert.toStr(yftbbh2));
                        yftbbhJson.put("value", Convert.toInt(tbsjbdwwts2));
                        yftbbhJsonList.add(yftbbhJson);
                    }
                    Object yftbbh3 = excelMap.get("印发通报编号3");
                    Object tbsjbdwwts3 = excelMap.get("通报涉及本单位问题数3");
                    if (yftbbh3 != null && !"".equals(tbsjbdwwts3)) {
                        JSONObject yftbbhJson = new JSONObject();
                        yftbbhJson.put("no", Convert.toStr(yftbbh3));
                        yftbbhJson.put("value", Convert.toInt(tbsjbdwwts3));
                        yftbbhJsonList.add(yftbbhJson);
                    }
                    Object yftbbh4 = excelMap.get("印发通报编号4");
                    Object tbsjbdwwts4 = excelMap.get("通报涉及本单位问题数4");
                    if (StrUtil.isNotBlank(Convert.toStr(yftbbh4)) && StrUtil.isNotBlank(Convert.toStr(tbsjbdwwts4))) {
                        JSONObject yftbbhJson = new JSONObject();
                        yftbbhJson.put("no", Convert.toStr(yftbbh4));
                        yftbbhJson.put("value", Convert.toInt(tbsjbdwwts4));
                        yftbbhJsonList.add(yftbbhJson);
                    }
                    Object yftbbh5 = excelMap.get("印发通报编号5");
                    Object tbsjbdwwts5 = excelMap.get("通报涉及本单位问题数5");
                    if (StrUtil.isNotBlank(Convert.toStr(yftbbh5)) && StrUtil.isNotBlank(Convert.toStr(tbsjbdwwts5))) {
                        JSONObject yftbbhJson = new JSONObject();
                        yftbbhJson.put("no", Convert.toStr(yftbbh5));
                        yftbbhJson.put("value", Convert.toInt(tbsjbdwwts5));
                        yftbbhJsonList.add(yftbbhJson);
                    }

                    List<JSONObject> zdmcJsonList = CollectionUtil.newArrayList();
                    Object zdmc1 = excelMap.get("制度名称1");
                    if (StrUtil.isNotBlank(Convert.toStr(zdmc1))) {
                        JSONObject zdmcJson = new JSONObject();
                        zdmcJson.put("name", Convert.toStr(zdmc1));
                        zdmcJsonList.add(zdmcJson);
                    }
                    Object zdmc2 = excelMap.get("制度名称2");
                    if (StrUtil.isNotBlank(Convert.toStr(zdmc2))) {
                        JSONObject zdmcJson = new JSONObject();
                        zdmcJson.put("name", Convert.toStr(zdmc2));
                        zdmcJsonList.add(zdmcJson);
                    }
                    Object zdmc3 = excelMap.get("制度名称3");
                    if (StrUtil.isNotBlank(Convert.toStr(zdmc3))) {
                        JSONObject zdmcJson = new JSONObject();
                        zdmcJson.put("name", Convert.toStr(zdmc3));
                        zdmcJsonList.add(zdmcJson);
                    }
                    Object zdmc4 = excelMap.get("制度名称4");
                    if (StrUtil.isNotBlank(Convert.toStr(zdmc4))) {
                        JSONObject zdmcJson = new JSONObject();
                        zdmcJson.put("name", Convert.toStr(zdmc4));
                        zdmcJsonList.add(zdmcJson);
                    }
                    Object zdmc5 = excelMap.get("制度名称5");
                    if (StrUtil.isNotBlank(Convert.toStr(zdmc5))) {
                        JSONObject zdmcJson = new JSONObject();
                        zdmcJson.put("name", Convert.toStr(zdmc5));
                        zdmcJsonList.add(zdmcJson);
                    }
                    jyProblemManagement.setCircularNo(yftbbhJsonList.toString());
                    jyProblemManagement.setEstablishedName(zdmcJsonList.toString());
                    if (jyProblemManagement.getDepartment() != null && jyProblemManagement.getProminentProblem() != null && jyProblemManagement.getProblemType() != null) {
                        jyDataMapper.insertSelective(jyProblemManagement);
                        jyReminderDataService.addReminder(jyProblemManagement);
                        // 插入记录
                        JyProblemManagementRecord jyProblemManagementRecord = new JyProblemManagementRecord();
                        jyProblemManagementRecord.setManagementId(jyProblemManagement.getId());
                        jyProblemManagementRecord.setProblemsFoundCount(jyProblemManagement.getProblemsFoundCount());
                        jyProblemManagementRecord.setClosedCount(jyProblemManagement.getClosedCount());
                        jyProblemManagementRecord.setInProgressCount(jyProblemManagement.getInProgressCount());
                        jyProblemManagementRecord.setDisciplinaryCases(jyProblemManagement.getDisciplinaryCases());
                        jyProblemManagementRecord.setPunishedPersons(jyProblemManagement.getPunishedPersons());
                        jyProblemManagementRecord.setDisciplinaryPunishedPersons(jyProblemManagement.getDisciplinaryPunishedPersons());
                        jyProblemManagementRecord.setCreatedTime(DateUtil.date());
                        jyProblemManagementRecordMapper.insert(jyProblemManagementRecord);
                    }
                });
                futures.add(future);
            }
            // 等待所有任务完成
            for (Future<?> future : futures) {
                try {
                    future.get();
                } catch (InterruptedException | ExecutionException e) {
                    log.error("任务执行出错", e);
                }
            }

            // 关闭线程池
            executorService.shutdown();
            return WrapMapper.wrap(Wrapper.SUCCESS_CODE, Wrapper.SUCCESS_MESSAGE, "导入数据成功");
        } catch (Exception e) {
            log.error("导入数据报错", e);
            return WrapMapper.wrap(Wrapper.ERROR_CODE, e.getMessage());
        }
    }

    @Override
    public void exportAllData(HttpServletResponse response, JSONObject reqJson) {
        try {
            List<JyProblemManagement> list = jyDataMapper.queryProblemManagementPage(reqJson);
            List<JSONObject> resList = CollectionUtil.newArrayList();
            List<Integer> mergeSizeList = CollectionUtil.newArrayList();

            ExcelWriter writer = ExcelUtil.getWriter(true);
            writer.addHeaderAlias("prominentProblem", "清单查摆问题表现");
            writer.addHeaderAlias("problemType", "突出问题类型");
            writer.addHeaderAlias("bigTypeName", "问题类别");
            writer.addHeaderAlias("rectificationMeasures", "整改措施");
            writer.addHeaderAlias("department", "责任单位");
            writer.addHeaderAlias("responsibleLeader", "牵头领导");
            writer.addHeaderAlias("supervisingLeader", "分管领导");
            writer.addHeaderAlias("completionDeadline", "完成时限");
            writer.addHeaderAlias("problemsFoundCount", "自查自纠发现问题数");
            writer.addHeaderAlias("closedCount", "已整治销号");
            writer.addHeaderAlias("inProgressCount", "整治处理中");
            writer.addHeaderAlias("establishedSystems", "建立制度数");
            writer.addHeaderAlias("establishedName", "制度名称");
            writer.addHeaderAlias("disciplinaryCases", "查处违纪违法案件数");
            writer.addHeaderAlias("punishedPersons", "组织处理人数");
            writer.addHeaderAlias("disciplinaryPunishedPersons", "纪律处分人数");
            writer.addHeaderAlias("circularNo", "印发通报编号");
            writer.addHeaderAlias("circularNoNumber", "通报涉及本单位问题数");
            writer.addHeaderAlias("recommendationLetters", "发出执纪执法建议（文书）数");
            writer.addHeaderAlias("remark", "其他情况");
            writer.addHeaderAlias("isTopTen", "是否属于“十大攻坚”");

            for (int i = 0; i < list.size(); i++) {
                JyProblemManagement jyProblemManagement = list.get(i);
                JSONObject resJson = new JSONObject();
                Integer mergeSize = 0;

                String prominentProblem = jyProblemManagement.getProminentProblem();
                String problemType = jyProblemManagement.getProblemType();
                String bigTypeName = jyProblemManagement.getBigTypeName();
                String rectificationMeasures = jyProblemManagement.getRectificationMeasures();
                String department = jyProblemManagement.getDepartment();
                String responsibleLeader = jyProblemManagement.getResponsibleLeader();
                String supervisingLeader = jyProblemManagement.getSupervisingLeader();
                String completionDeadline = jyProblemManagement.getCompletionDeadline();
                Integer problemsFoundCount = jyProblemManagement.getProblemsFoundCount();
                Integer closedCount = jyProblemManagement.getClosedCount();
                Integer inProgressCount = jyProblemManagement.getInProgressCount();
                Integer establishedSystems = jyProblemManagement.getEstablishedSystems();
                Integer disciplinaryCases = jyProblemManagement.getDisciplinaryCases();
                Integer punishedPersons = jyProblemManagement.getPunishedPersons();
                Integer disciplinaryPunishedPersons = jyProblemManagement.getDisciplinaryPunishedPersons();
                Integer recommendationLetters = jyProblemManagement.getRecommendationLetters();
                String remark = jyProblemManagement.getRemark();
                Integer isTopTen = jyProblemManagement.getIsTopTen();

                String establishedName = jyProblemManagement.getEstablishedName();
                List<String> establishedNames = CollectionUtil.newArrayList();
                if (establishedName != null) {
                    JSONArray establishedNameList = JSONArray.parseArray(establishedName);
                    for (int i1 = 0; i1 < establishedNameList.size(); i1++) {
                        JSONObject establishedNameJson = establishedNameList.getJSONObject(i1);
                        String name = establishedNameJson.getString("name");
                        if (StrUtil.isNotBlank(name)) {
                            mergeSize++;
                            establishedNames.add(name);
                        }
                    }
                }

                String circularNo = jyProblemManagement.getCircularNo();
                List<String> circularNos = CollectionUtil.newArrayList();
                List<String> circularNoNumbers = CollectionUtil.newArrayList();
                if (circularNo != null) {
                    JSONArray circularNoList = JSONArray.parseArray(circularNo);
                    int circularNoSize = 0;
                    for (int i1 = 0; i1 < circularNoList.size(); i1++) {
                        JSONObject circularNoJson = circularNoList.getJSONObject(i1);
                        String no = circularNoJson.getString("no");
                        String value = circularNoJson.getString("value");
                        if (StrUtil.isNotBlank(no)) {
                            circularNos.add(no);
                        }
                        if (StrUtil.isNotBlank(value)) {
                            circularNoNumbers.add(value);
                        }
                        if (StrUtil.isNotBlank(no) || StrUtil.isNotBlank(value)) {
                            circularNoSize++;
                        }
                    }
                    if (circularNoSize > mergeSize) {
                        mergeSize = circularNoSize;
                    }
                }

                resJson.put("prominentProblem", prominentProblem);
                resJson.put("problemType", problemType);
                resJson.put("bigTypeName", bigTypeName);
                resJson.put("rectificationMeasures", rectificationMeasures);
                resJson.put("department", department);
                resJson.put("responsibleLeader", responsibleLeader);
                resJson.put("supervisingLeader", supervisingLeader);
                resJson.put("completionDeadline", completionDeadline);
                resJson.put("problemsFoundCount", problemsFoundCount);
                resJson.put("closedCount", closedCount);
                resJson.put("inProgressCount", inProgressCount);
                resJson.put("establishedSystems", establishedSystems);
                resJson.put("disciplinaryCases", disciplinaryCases);
                resJson.put("punishedPersons", punishedPersons);
                resJson.put("disciplinaryPunishedPersons", disciplinaryPunishedPersons);
                resJson.put("recommendationLetters", recommendationLetters);
                resJson.put("remark", remark);
                resJson.put("establishedName", null);
                resJson.put("circularNo", null);
                resJson.put("circularNoNumber", null);
                mergeSizeList.add(mergeSize);
                if (isTopTen == 1) {
                    resJson.put("isTopTen", "是");
                } else {
                    resJson.put("isTopTen", "否");
                }
                resList.add(resJson);

                for (int j = 0; j < mergeSize; j++) {
                    JSONObject resJsonNew = new JSONObject();
                    resJsonNew.put("prominentProblem", null);
                    resJsonNew.put("problemType", null);
                    resJsonNew.put("bigTypeName", null);
                    resJsonNew.put("rectificationMeasures", null);
                    resJsonNew.put("department", null);
                    resJsonNew.put("responsibleLeader", null);
                    resJsonNew.put("supervisingLeader", null);
                    resJsonNew.put("completionDeadline", null);
                    resJsonNew.put("problemsFoundCount", null);
                    resJsonNew.put("closedCount", null);
                    resJsonNew.put("inProgressCount", null);
                    resJsonNew.put("establishedSystems", null);
                    resJsonNew.put("disciplinaryCases", null);
                    resJsonNew.put("punishedPersons", null);
                    resJsonNew.put("disciplinaryPunishedPersons", null);
                    resJsonNew.put("recommendationLetters", null);
                    resJsonNew.put("remark", null);
                    resJsonNew.put("establishedName", null);
                    resJsonNew.put("circularNo", null);
                    resJsonNew.put("circularNoNumber", null);
                    resJsonNew.put("isTopTen", null);
                    if (establishedNames.size() == 1) {
                        String establishedNameNew = establishedNames.get(0);
                        resJson.put("establishedName", establishedNameNew);
                    } else if (establishedNames.size() > 1) {
                        if (j == 0) {
                            String establishedNameNew = establishedNames.get(0);
                            resJson.put("establishedName", establishedNameNew);
                        } else if (establishedNames.size() > j) {
                            String establishedNameNew = establishedNames.get(j);
                            resJsonNew.put("establishedName", establishedNameNew);
                        }
                    }
                    if (circularNos.size() == 1) {
                        String circularNoNew = circularNos.get(0);
                        resJson.put("circularNo", circularNoNew);
                    } else if (circularNos.size() > 1) {
                        if (j == 0) {
                            String circularNoNew = circularNos.get(0);
                            resJson.put("circularNo", circularNoNew);
                        } else if (circularNos.size() > j) {
                            String circularNoNew = circularNos.get(j);
                            resJsonNew.put("circularNo", circularNoNew);
                        }
                    }

                    if (circularNoNumbers.size() == 1) {
                        String circularNoNumberNew = circularNoNumbers.get(0);
                        resJson.put("circularNoNumber", circularNoNumberNew);
                    } else if (circularNoNumbers.size() > 1) {
                        if (j == 0) {
                            String circularNoNumberNew = circularNoNumbers.get(0);
                            resJson.put("circularNoNumber", circularNoNumberNew);
                        } else if (circularNoNumbers.size() > j) {
                            String circularNoNumberNew = circularNoNumbers.get(j);
                            resJsonNew.put("circularNoNumber", circularNoNumberNew);
                        }
                    }
                    if (StrUtil.isNotBlank(resJsonNew.getString("circularNo")) || StrUtil.isNotBlank(resJsonNew.getString("circularNoNumber")) || StrUtil.isNotBlank(resJsonNew.getString("establishedName"))) {
                        resJsonNew.put("mergeSize", 1);
                        resList.add(resJsonNew);
                        mergeSizeList.add(1);
                    }
                }
            }
            writer.write(resList, true);
            for (int a = 0; a < 21; a++) {
                writer.setColumnWidth(a, 30);
                if (a == 12 || a == 16 || a == 17) {
                    continue;
                }
                for (int b = 0; b < resList.size(); b++) {
                    writer.setRowHeight(b, 20);
                    Integer mergeSize = mergeSizeList.get(b);
                    if (mergeSize > 1) {
                        Cell cell1 = writer.getCell(a, b + 1);
                        // 根据单元格类型获取值
                        CellType cellType = cell1.getCellType();
                        String cellValue = null;
                        switch (cellType) {
                            case STRING:
                                cellValue = cell1.getStringCellValue();
                                writer.merge(b + 1, b + mergeSize, a, a, cellValue, false);
                                break;
                            case NUMERIC:
                                cellValue = String.valueOf(cell1.getNumericCellValue());
                                writer.merge(b + 1, b + mergeSize, a, a, cellValue, false);
                                break;
                        }
                    }

                }
            }

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=allData.xlsx");
            ServletOutputStream out = response.getOutputStream();

            writer.flush(out, true);
            writer.close();
            IoUtil.close(out);
        } catch (Exception e) {
            log.error("导出数据报错", e);
        }
    }

    @Override
    public Wrapper<Object> dataByDepartment(HttpServletResponse response, JSONObject reqJson) {
        try {
            NumberFormat nt = NumberFormat.getPercentInstance();
            nt.setMinimumFractionDigits(2);

            Boolean isExport = reqJson.getBoolean("isExport");
            List<JyProblemManagement> list = jyDataMapper.queryProblemManagementPage(reqJson);
            List<JSONObject> resList = CollectionUtil.newArrayList();

            List<String> departmentList = list.stream().map(JyProblemManagement::getDepartment).distinct().collect(Collectors.toList());
            for (String department : departmentList) {
                if (StrUtil.isBlank(department)) {
                    continue;
                }
                JSONObject resJson = new JSONObject();
                List<JyProblemManagement> listByDepartment = list.stream().filter(JyProblemManagement -> department.equals(JyProblemManagement.getDepartment())).collect(Collectors.toList());
                int problemsFoundCountSum = listByDepartment.stream()
                        .map(JyProblemManagement::getProblemsFoundCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int inProgressCountSum = listByDepartment.stream()
                        .map(JyProblemManagement::getInProgressCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int closedCountSum = listByDepartment.stream()
                        .map(JyProblemManagement::getClosedCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int problemsFoundCountSumNew = 0;
                int closedCountSumNew = 0;
                for (JyProblemManagement jyProblemManagement : listByDepartment) {
                    int problemsFoundCountNew = 0;
                    int closedCountNew = 0;
                    Long id = jyProblemManagement.getId();
                    JyProblemManagementRecord jyProblemManagementRecord = new JyProblemManagementRecord();
                    jyProblemManagementRecord.setManagementId(id);
                    List<JyProblemManagementRecord> jyProblemManagementRecordList = jyProblemManagementRecordMapper.select(jyProblemManagementRecord);
                    if (CollectionUtil.isNotEmpty(jyProblemManagementRecordList)) {
                        if (jyProblemManagementRecordList.size() == 1) {
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(0);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagement.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagement.getClosedCount();
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        } else {
                            jyProblemManagementRecordList.sort(Comparator.comparing(JyProblemManagementRecord::getCreatedTime));
                            JyProblemManagementRecord jyProblemManagementRecordOld = jyProblemManagementRecordList.get(0);
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(jyProblemManagementRecordList.size() - 1);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagementRecordOld.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagementRecordOld.getClosedCount();
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        }
                        problemsFoundCountSumNew = problemsFoundCountSumNew + problemsFoundCountNew;
                        closedCountSumNew = closedCountSumNew + closedCountNew;
                    }
                }
                int establishedSystemsSum = listByDepartment.stream()
                        .map(JyProblemManagement::getEstablishedSystems)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryCasesSum = listByDepartment.stream()
                        .map(JyProblemManagement::getDisciplinaryCases)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int punishedPersonsSum = listByDepartment.stream()
                        .map(JyProblemManagement::getPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryPunishedPersonsSum = listByDepartment.stream()
                        .map(JyProblemManagement::getDisciplinaryPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int recommendationLettersSum = listByDepartment.stream()
                        .map(JyProblemManagement::getRecommendationLetters)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int circularNoSum = listByDepartment.stream()
                        .map(JyProblemManagement::getCircularNo)
                        .filter(StrUtil::isNotBlank)
                        .mapToInt(circularNo -> {
                            try {
                                JSONArray jsonArray = JSONArray.parseArray(circularNo);
                                return jsonArray.size();
                            } catch (Exception e) {
                                // 解析失败时返回 0
                                return 0;
                            }
                        })
                        .sum();
                int circularIssuesSum = listByDepartment.stream()
                        .map(JyProblemManagement::getCircularIssues)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();

                resJson.put("department", department);
                resJson.put("problemsFoundCount", problemsFoundCountSum);
                resJson.put("closedCount", closedCountSum);
                resJson.put("inProgressCountSum", inProgressCountSum);
                resJson.put("problemsFoundCountNew", problemsFoundCountSumNew);
                resJson.put("closedCountNew", closedCountSumNew);
                resJson.put("percent", nt.format(problemsFoundCountSum == 0 ? 0.0d : (double) inProgressCountSum / problemsFoundCountSum));
                resJson.put("establishedSystems", establishedSystemsSum);
                resJson.put("disciplinaryCases", disciplinaryCasesSum);
                resJson.put("punishedPersons", punishedPersonsSum);
                resJson.put("disciplinaryPunishedPersons", disciplinaryPunishedPersonsSum);
                resJson.put("recommendationLetters", recommendationLettersSum);
                resJson.put("circularCount", circularNoSum);
                resJson.put("circularIssues", circularIssuesSum);
                resList.add(resJson);
            }
            if (isExport != null && isExport) {
                ExcelWriter writer = ExcelUtil.getWriter(true);
                writer.addHeaderAlias("department", "责任单位");
                writer.addHeaderAlias("problemsFoundCount", "自查自纠发现问题数");
                writer.addHeaderAlias("inProgressCountSum", "整治处理中问题数");
                writer.addHeaderAlias("closedCount", "已整治销号总数");
                writer.addHeaderAlias("problemsFoundCountNew", "新增问题数");
                writer.addHeaderAlias("closedCountNew", "新增销号数");
                writer.addHeaderAlias("percent", "整改率");
                writer.addHeaderAlias("establishedSystems", "建立制度数");
                writer.addHeaderAlias("disciplinaryCases", "查处违纪违法案件数");
                writer.addHeaderAlias("punishedPersons", "组织处理人数");
                writer.addHeaderAlias("disciplinaryPunishedPersons", "纪律处分人数");
                writer.addHeaderAlias("recommendationLetters", "发出执纪执法建议（文书）数");
                writer.addHeaderAlias("circularCount", "印发通报期数");
                writer.addHeaderAlias("circularIssues", "通报涉及问题数");

                writer.write(resList, true);
                for (int i = 0; i < 7; i++) {
                    writer.setColumnWidth(i, 30);
                }
                for (int i = 0; i < list.size(); i++) {
                    writer.setColumnWidth(i, 30);
                }

                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=dataByDepartment.xlsx");
                ServletOutputStream out = response.getOutputStream();

                writer.flush(out, true);
                writer.close();
                IoUtil.close(out);
                return null;
            } else {
                return WrapMapper.wrap(Wrapper.SUCCESS_CODE, Wrapper.SUCCESS_MESSAGE, resList);
            }
        } catch (Exception e) {
            log.error("导出数据报错", e);
            return WrapMapper.wrap(Wrapper.ERROR_CODE, e.getMessage());
        }
    }

    @Override
    public Wrapper<Object> dataByArea(HttpServletResponse response, JSONObject reqJson) {
        try {
            NumberFormat nt = NumberFormat.getPercentInstance();
            nt.setMinimumFractionDigits(2);

            Boolean isExport = reqJson.getBoolean("isExport");
            List<JyProblemManagement> list = jyDataMapper.queryProblemManagementPage(reqJson);
            List<JSONObject> resList = CollectionUtil.newArrayList();

            List<String> areaNameList = list.stream().map(JyProblemManagement::getAreaName).distinct().collect(Collectors.toList());
            for (String areaName : areaNameList) {
                if (StrUtil.isBlank(areaName)) {
                    continue;
                }
                JSONObject resJson = new JSONObject();
                List<JyProblemManagement> listByAreaName = list.stream().filter(JyProblemManagement -> areaName.equals(JyProblemManagement.getAreaName())).collect(Collectors.toList());
                int problemsFoundCountSum = listByAreaName.stream()
                        .map(JyProblemManagement::getProblemsFoundCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int inProgressCountSum = listByAreaName.stream()
                        .map(JyProblemManagement::getInProgressCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int closedCountSum = listByAreaName.stream()
                        .map(JyProblemManagement::getClosedCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();

                int problemsFoundCountSumNew = 0;
                int closedCountSumNew = 0;
                for (JyProblemManagement jyProblemManagement : listByAreaName) {
                    int problemsFoundCountNew = 0;
                    int closedCountNew = 0;
                    Long id = jyProblemManagement.getId();
                    JyProblemManagementRecord jyProblemManagementRecord = new JyProblemManagementRecord();
                    jyProblemManagementRecord.setManagementId(id);
                    List<JyProblemManagementRecord> jyProblemManagementRecordList = jyProblemManagementRecordMapper.select(jyProblemManagementRecord);
                    if (CollectionUtil.isNotEmpty(jyProblemManagementRecordList)) {
                        if (jyProblemManagementRecordList.size() == 1) {
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(0);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagement.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagement.getClosedCount();
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        } else {
                            jyProblemManagementRecordList.sort(Comparator.comparing(JyProblemManagementRecord::getCreatedTime));
                            JyProblemManagementRecord jyProblemManagementRecordOld = jyProblemManagementRecordList.get(0);
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(jyProblemManagementRecordList.size() - 1);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagementRecordOld.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagementRecordOld.getClosedCount();
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        }
                        problemsFoundCountSumNew = problemsFoundCountSumNew + problemsFoundCountNew;
                        closedCountSumNew = closedCountSumNew + closedCountNew;
                    }
                }
                int establishedSystemsSum = listByAreaName.stream()
                        .map(JyProblemManagement::getEstablishedSystems)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryCasesSum = listByAreaName.stream()
                        .map(JyProblemManagement::getDisciplinaryCases)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int punishedPersonsSum = listByAreaName.stream()
                        .map(JyProblemManagement::getPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryPunishedPersonsSum = listByAreaName.stream()
                        .map(JyProblemManagement::getDisciplinaryPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int recommendationLettersSum = listByAreaName.stream()
                        .map(JyProblemManagement::getRecommendationLetters)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int circularNoSum = listByAreaName.stream()
                        .map(JyProblemManagement::getCircularNo)
                        .filter(StrUtil::isNotBlank)
                        .mapToInt(circularNo -> {
                            try {
                                JSONArray jsonArray = JSONArray.parseArray(circularNo);
                                return jsonArray.size();
                            } catch (Exception e) {
                                // 解析失败时返回 0
                                return 0;
                            }
                        })
                        .sum();
                int circularIssuesSum = listByAreaName.stream()
                        .map(JyProblemManagement::getCircularIssues)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();

                resJson.put("areaName", areaName);
                resJson.put("problemsFoundCount", problemsFoundCountSum);
                resJson.put("closedCount", closedCountSum);
                resJson.put("inProgressCountSum", inProgressCountSum);
                resJson.put("problemsFoundCountNew", problemsFoundCountSumNew);
                resJson.put("closedCountNew", closedCountSumNew);
                resJson.put("percent", nt.format(problemsFoundCountSum == 0 ? 0.0d : (double) inProgressCountSum / problemsFoundCountSum));
                resJson.put("establishedSystems", establishedSystemsSum);
                resJson.put("disciplinaryCases", disciplinaryCasesSum);
                resJson.put("punishedPersons", punishedPersonsSum);
                resJson.put("disciplinaryPunishedPersons", disciplinaryPunishedPersonsSum);
                resJson.put("recommendationLetters", recommendationLettersSum);
                resJson.put("circularCount", circularNoSum);
                resJson.put("circularIssues", circularIssuesSum);
                resList.add(resJson);
            }
            if (isExport != null && isExport) {
                ExcelWriter writer = ExcelUtil.getWriter(true);
                writer.addHeaderAlias("areaName", "区划名称");
                writer.addHeaderAlias("problemsFoundCount", "自查自纠发现问题数");
                writer.addHeaderAlias("inProgressCountSum", "整治处理中问题数");
                writer.addHeaderAlias("closedCount", "已整治销号总数");
                writer.addHeaderAlias("problemsFoundCountNew", "新增问题数");
                writer.addHeaderAlias("closedCountNew", "新增销号数");
                writer.addHeaderAlias("percent", "整改率");
                writer.addHeaderAlias("establishedSystems", "建立制度数");
                writer.addHeaderAlias("disciplinaryCases", "查处违纪违法案件数");
                writer.addHeaderAlias("punishedPersons", "组织处理人数");
                writer.addHeaderAlias("disciplinaryPunishedPersons", "纪律处分人数");
                writer.addHeaderAlias("recommendationLetters", "发出执纪执法建议（文书）数");
                writer.addHeaderAlias("circularCount", "印发通报期数");
                writer.addHeaderAlias("circularIssues", "通报涉及问题数");

                writer.write(resList, true);
                for (int i = 0; i < 7; i++) {
                    writer.setColumnWidth(i, 30);
                }
                for (int i = 0; i < list.size(); i++) {
                    writer.setColumnWidth(i, 30);
                }

                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=dataByAreaName.xlsx");
                ServletOutputStream out = response.getOutputStream();

                writer.flush(out, true);
                writer.close();
                IoUtil.close(out);
                return null;
            } else {
                return WrapMapper.wrap(Wrapper.SUCCESS_CODE, Wrapper.SUCCESS_MESSAGE, resList);
            }
        } catch (Exception e) {
            log.error("导出数据报错", e);
            return WrapMapper.wrap(Wrapper.ERROR_CODE, e.getMessage());
        }
    }

    @Override
    public Wrapper<Object> dataByBigTypeName(HttpServletResponse response, JSONObject reqJson) {
        try {
            NumberFormat nt = NumberFormat.getPercentInstance();
            nt.setMinimumFractionDigits(2);

            Boolean isExport = reqJson.getBoolean("isExport");
            List<JyProblemManagement> list = jyDataMapper.queryProblemManagementPage(reqJson);
            List<JSONObject> resList = CollectionUtil.newArrayList();

            List<String> bigTypeNameList = list.stream().map(JyProblemManagement::getBigTypeName).distinct().collect(Collectors.toList());
            for (String bigTypeName : bigTypeNameList) {
                if (StrUtil.isBlank(bigTypeName)) {
                    continue;
                }
                JSONObject resJson = new JSONObject();
                List<JyProblemManagement> listByBigTypeName = list.stream().filter(JyProblemManagement -> bigTypeName.equals(JyProblemManagement.getBigTypeName())).collect(Collectors.toList());
                int problemsFoundCountSum = listByBigTypeName.stream()
                        .map(JyProblemManagement::getProblemsFoundCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int inProgressCountSum = listByBigTypeName.stream()
                        .map(JyProblemManagement::getInProgressCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int closedCountSum = listByBigTypeName.stream()
                        .map(JyProblemManagement::getClosedCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();

                int problemsFoundCountSumNew = 0;
                int closedCountSumNew = 0;
                for (JyProblemManagement jyProblemManagement : listByBigTypeName) {
                    int problemsFoundCountNew = 0;
                    int closedCountNew = 0;
                    Long id = jyProblemManagement.getId();
                    JyProblemManagementRecord jyProblemManagementRecord = new JyProblemManagementRecord();
                    jyProblemManagementRecord.setManagementId(id);
                    List<JyProblemManagementRecord> jyProblemManagementRecordList = jyProblemManagementRecordMapper.select(jyProblemManagementRecord);
                    if (CollectionUtil.isNotEmpty(jyProblemManagementRecordList)) {
                        if (jyProblemManagementRecordList.size() == 1) {
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(0);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagement.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagement.getClosedCount();
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        } else {
                            jyProblemManagementRecordList.sort(Comparator.comparing(JyProblemManagementRecord::getCreatedTime));
                            JyProblemManagementRecord jyProblemManagementRecordOld = jyProblemManagementRecordList.get(0);
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(jyProblemManagementRecordList.size() - 1);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagementRecordOld.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagementRecordOld.getClosedCount();
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        }
                        problemsFoundCountSumNew = problemsFoundCountSumNew + problemsFoundCountNew;
                        closedCountSumNew = closedCountSumNew + closedCountNew;
                    }
                }
                int establishedSystemsSum = listByBigTypeName.stream()
                        .map(JyProblemManagement::getEstablishedSystems)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryCasesSum = listByBigTypeName.stream()
                        .map(JyProblemManagement::getDisciplinaryCases)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int punishedPersonsSum = listByBigTypeName.stream()
                        .map(JyProblemManagement::getPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryPunishedPersonsSum = listByBigTypeName.stream()
                        .map(JyProblemManagement::getDisciplinaryPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int recommendationLettersSum = listByBigTypeName.stream()
                        .map(JyProblemManagement::getRecommendationLetters)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int circularNoSum = listByBigTypeName.stream()
                        .map(JyProblemManagement::getCircularNo)
                        .filter(StrUtil::isNotBlank)
                        .mapToInt(circularNo -> {
                            try {
                                JSONArray jsonArray = JSONArray.parseArray(circularNo);
                                return jsonArray.size();
                            } catch (Exception e) {
                                // 解析失败时返回 0
                                return 0;
                            }
                        })
                        .sum();
                int circularIssuesSum = listByBigTypeName.stream()
                        .map(JyProblemManagement::getCircularIssues)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();

                resJson.put("bigTypeName", bigTypeName);
                resJson.put("problemsFoundCount", problemsFoundCountSum);
                resJson.put("closedCount", closedCountSum);
                resJson.put("inProgressCountSum", inProgressCountSum);
                resJson.put("problemsFoundCountNew", problemsFoundCountSumNew);
                resJson.put("closedCountNew", closedCountSumNew);
                resJson.put("percent", nt.format(problemsFoundCountSum == 0 ? 0.0d : (double) inProgressCountSum / problemsFoundCountSum));
                resJson.put("establishedSystems", establishedSystemsSum);
                resJson.put("disciplinaryCases", disciplinaryCasesSum);
                resJson.put("punishedPersons", punishedPersonsSum);
                resJson.put("disciplinaryPunishedPersons", disciplinaryPunishedPersonsSum);
                resJson.put("recommendationLetters", recommendationLettersSum);
                resJson.put("circularCount", circularNoSum);
                resJson.put("circularIssues", circularIssuesSum);

                resList.add(resJson);
            }

            ExcelWriter writer = ExcelUtil.getWriter(true);
            writer.addHeaderAlias("bigTypeName", "问题类型");
            writer.addHeaderAlias("problemsFoundCount", "自查自纠发现问题数");
            writer.addHeaderAlias("inProgressCountSum", "整治处理中问题数");
            writer.addHeaderAlias("closedCount", "已整治销号总数");
            writer.addHeaderAlias("problemsFoundCountNew", "新增问题数");
            writer.addHeaderAlias("closedCountNew", "新增销号数");
            writer.addHeaderAlias("percent", "整改率");
            writer.addHeaderAlias("establishedSystems", "建立制度数");
            writer.addHeaderAlias("disciplinaryCases", "查处违纪违法案件数");
            writer.addHeaderAlias("punishedPersons", "组织处理人数");
            writer.addHeaderAlias("disciplinaryPunishedPersons", "纪律处分人数");
            writer.addHeaderAlias("recommendationLetters", "发出执纪执法建议（文书）数");
            writer.addHeaderAlias("circularCount", "印发通报期数");
            writer.addHeaderAlias("circularIssues", "通报涉及问题数");

            if (isExport != null && isExport) {
                writer.write(resList, true);
                for (int i = 0; i < 7; i++) {
                    writer.setColumnWidth(i, 30);
                }
                for (int i = 0; i < list.size(); i++) {
                    writer.setColumnWidth(i, 30);
                }

                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=dataByBigTypeName.xlsx");
                ServletOutputStream out = response.getOutputStream();

                writer.flush(out, true);
                writer.close();
                IoUtil.close(out);
                return null;
            } else {
                return WrapMapper.wrap(Wrapper.SUCCESS_CODE, Wrapper.SUCCESS_MESSAGE, resList);
            }
        } catch (Exception e) {
            log.error("导出数据报错", e);
            return WrapMapper.wrap(Wrapper.ERROR_CODE, e.getMessage());
        }
    }

    @Override
    public Wrapper<Object> dataByProminentProblem(HttpServletResponse response, JSONObject reqJson) {
        try {
            NumberFormat nt = NumberFormat.getPercentInstance();
            nt.setMinimumFractionDigits(2);

            Boolean isExport = reqJson.getBoolean("isExport");
            List<JyProblemManagement> list = jyDataMapper.queryProblemManagementPage(reqJson);
            List<JSONObject> resList = CollectionUtil.newArrayList();

            List<String> prominentProblemList = list.stream().map(JyProblemManagement::getProminentProblem).distinct().collect(Collectors.toList());
            for (String prominentProblem : prominentProblemList) {
                if (StrUtil.isBlank(prominentProblem)) {
                    continue;
                }
                JSONObject resJson = new JSONObject();
                List<JyProblemManagement> listByProminentProblem = list.stream().filter(JyProblemManagement -> prominentProblem.equals(JyProblemManagement.getProminentProblem())).collect(Collectors.toList());
                int problemsFoundCountSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getProblemsFoundCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int inProgressCountSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getInProgressCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int closedCountSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getClosedCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();

                int problemsFoundCountSumNew = 0;
                int closedCountSumNew = 0;
                for (JyProblemManagement jyProblemManagement : listByProminentProblem) {
                    int problemsFoundCountNew = 0;
                    int closedCountNew = 0;
                    Long id = jyProblemManagement.getId();
                    JyProblemManagementRecord jyProblemManagementRecord = new JyProblemManagementRecord();
                    jyProblemManagementRecord.setManagementId(id);
                    List<JyProblemManagementRecord> jyProblemManagementRecordList = jyProblemManagementRecordMapper.select(jyProblemManagementRecord);
                    if (CollectionUtil.isNotEmpty(jyProblemManagementRecordList)) {
                        if (jyProblemManagementRecordList.size() == 1) {
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(0);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagement.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagement.getClosedCount();
                            //印发通报期数
                            //通报涉及问题数
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        } else {
                            jyProblemManagementRecordList.sort(Comparator.comparing(JyProblemManagementRecord::getCreatedTime));
                            JyProblemManagementRecord jyProblemManagementRecordOld = jyProblemManagementRecordList.get(0);
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(jyProblemManagementRecordList.size() - 1);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagementRecordOld.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagementRecordOld.getClosedCount();
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        }
                        problemsFoundCountSumNew = problemsFoundCountSumNew + problemsFoundCountNew;
                        closedCountSumNew = closedCountSumNew + closedCountNew;
                    }
                }
                int establishedSystemsSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getEstablishedSystems)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryCasesSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getDisciplinaryCases)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int punishedPersonsSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryPunishedPersonsSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getDisciplinaryPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int recommendationLettersSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getRecommendationLetters)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int circularNoSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getCircularNo)
                        .filter(StrUtil::isNotBlank)
                        .mapToInt(circularNo -> {
                            try {
                                JSONArray jsonArray = JSONArray.parseArray(circularNo);
                                return jsonArray.size();
                            } catch (Exception e) {
                                // 解析失败时返回 0
                                return 0;
                            }
                        })
                        .sum();
                int circularIssuesSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getCircularIssues)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();

                resJson.put("prominentProblem", prominentProblem);
                resJson.put("problemsFoundCount", problemsFoundCountSum);
                resJson.put("closedCount", closedCountSum);
                resJson.put("inProgressCountSum", inProgressCountSum);
                resJson.put("problemsFoundCountNew", problemsFoundCountSumNew);
                resJson.put("closedCountNew", closedCountSumNew);
                resJson.put("percent", nt.format(problemsFoundCountSum == 0 ? 0.0d : (double) inProgressCountSum / problemsFoundCountSum));
                resJson.put("establishedSystems", establishedSystemsSum);
                resJson.put("disciplinaryCases", disciplinaryCasesSum);
                resJson.put("punishedPersons", punishedPersonsSum);
                resJson.put("disciplinaryPunishedPersons", disciplinaryPunishedPersonsSum);
                resJson.put("recommendationLetters", recommendationLettersSum);
                resJson.put("circularCount", circularNoSum);
                resJson.put("circularIssues", circularIssuesSum);
                resList.add(resJson);
            }

            if (isExport != null && isExport) {
                ExcelWriter writer = ExcelUtil.getWriter(true);
                writer.addHeaderAlias("prominentProblem", "清单查摆问题表现");
                writer.addHeaderAlias("problemsFoundCount", "自查自纠发现问题数");
                writer.addHeaderAlias("inProgressCountSum", "整治处理中问题数");
                writer.addHeaderAlias("closedCount", "已整治销号总数");
                writer.addHeaderAlias("problemsFoundCountNew", "新增问题数");
                writer.addHeaderAlias("closedCountNew", "新增销号数");
                writer.addHeaderAlias("percent", "整改率");
                writer.addHeaderAlias("establishedSystems", "建立制度数");
                writer.addHeaderAlias("disciplinaryCases", "查处违纪违法案件数");
                writer.addHeaderAlias("punishedPersons", "组织处理人数");
                writer.addHeaderAlias("disciplinaryPunishedPersons", "纪律处分人数");
                writer.addHeaderAlias("recommendationLetters", "发出执纪执法建议（文书）数");
                writer.addHeaderAlias("circularCount", "印发通报期数");
                writer.addHeaderAlias("circularIssues", "通报涉及问题数");

                writer.write(resList, true);
                for (int i = 0; i < 7; i++) {
                    writer.setColumnWidth(i, 30);
                }
                for (int i = 0; i < list.size(); i++) {
                    writer.setColumnWidth(i, 30);
                }

                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=dataByProminentProblem.xlsx");
                ServletOutputStream out = response.getOutputStream();

                writer.flush(out, true);
                writer.close();
                IoUtil.close(out);
                return null;
            } else {
                return WrapMapper.wrap(Wrapper.SUCCESS_CODE, Wrapper.SUCCESS_MESSAGE, resList);
            }
        } catch (Exception e) {
            log.error("导出数据报错", e);
            return WrapMapper.wrap(Wrapper.ERROR_CODE, e.getMessage());
        }
    }

    @Override
    public Wrapper<Object> dataByProblemType(HttpServletResponse response, JSONObject reqJson) {
        try {
            NumberFormat nt = NumberFormat.getPercentInstance();
            nt.setMinimumFractionDigits(2);

            Boolean isExport = reqJson.getBoolean("isExport");
            List<JyProblemManagement> list = jyDataMapper.queryProblemManagementPage(reqJson);
            List<JSONObject> resList = CollectionUtil.newArrayList();

            List<String> problemTypeList = list.stream().map(JyProblemManagement::getProblemType).distinct().collect(Collectors.toList());
            for (String problemType : problemTypeList) {
                if (StrUtil.isBlank(problemType)) {
                    continue;
                }
                JSONObject resJson = new JSONObject();
                List<JyProblemManagement> listByProblemType = list.stream().filter(JyProblemManagement -> problemType.equals(JyProblemManagement.getProblemType())).collect(Collectors.toList());
                int problemsFoundCountSum = listByProblemType.stream()
                        .map(JyProblemManagement::getProblemsFoundCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int inProgressCountSum = listByProblemType.stream()
                        .map(JyProblemManagement::getInProgressCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int closedCountSum = listByProblemType.stream()
                        .map(JyProblemManagement::getClosedCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();

                int problemsFoundCountSumNew = 0;
                int closedCountSumNew = 0;
                for (JyProblemManagement jyProblemManagement : listByProblemType) {
                    int problemsFoundCountNew = 0;
                    int closedCountNew = 0;
                    Long id = jyProblemManagement.getId();
                    JyProblemManagementRecord jyProblemManagementRecord = new JyProblemManagementRecord();
                    jyProblemManagementRecord.setManagementId(id);
                    List<JyProblemManagementRecord> jyProblemManagementRecordList = jyProblemManagementRecordMapper.select(jyProblemManagementRecord);
                    if (CollectionUtil.isNotEmpty(jyProblemManagementRecordList)) {
                        if (jyProblemManagementRecordList.size() == 1) {
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(0);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagement.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagement.getClosedCount();
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        } else {
                            jyProblemManagementRecordList.sort(Comparator.comparing(JyProblemManagementRecord::getCreatedTime));
                            JyProblemManagementRecord jyProblemManagementRecordOld = jyProblemManagementRecordList.get(0);
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(jyProblemManagementRecordList.size() - 1);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagementRecordOld.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagementRecordOld.getClosedCount();
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        }
                        problemsFoundCountSumNew = problemsFoundCountSumNew + problemsFoundCountNew;
                        closedCountSumNew = closedCountSumNew + closedCountNew;
                    }
                }
                int establishedSystemsSum = listByProblemType.stream()
                        .map(JyProblemManagement::getEstablishedSystems)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryCasesSum = listByProblemType.stream()
                        .map(JyProblemManagement::getDisciplinaryCases)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int punishedPersonsSum = listByProblemType.stream()
                        .map(JyProblemManagement::getPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryPunishedPersonsSum = listByProblemType.stream()
                        .map(JyProblemManagement::getDisciplinaryPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int recommendationLettersSum = listByProblemType.stream()
                        .map(JyProblemManagement::getRecommendationLetters)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int circularNoSum = listByProblemType.stream()
                        .map(JyProblemManagement::getCircularNo)
                        .filter(StrUtil::isNotBlank)
                        .mapToInt(circularNo -> {
                            try {
                                JSONArray jsonArray = JSONArray.parseArray(circularNo);
                                return jsonArray.size();
                            } catch (Exception e) {
                                // 解析失败时返回 0
                                return 0;
                            }
                        })
                        .sum();
                int circularIssuesSum = listByProblemType.stream()
                        .map(JyProblemManagement::getCircularIssues)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();

                resJson.put("problemType", problemType);
                resJson.put("problemsFoundCount", problemsFoundCountSum);
                resJson.put("closedCount", closedCountSum);
                resJson.put("inProgressCountSum", inProgressCountSum);
                resJson.put("problemsFoundCountNew", problemsFoundCountSumNew);
                resJson.put("closedCountNew", closedCountSumNew);
                resJson.put("percent", nt.format(problemsFoundCountSum == 0 ? 0.0d : (double) inProgressCountSum / problemsFoundCountSum));
                resJson.put("establishedSystems", establishedSystemsSum);
                resJson.put("disciplinaryCases", disciplinaryCasesSum);
                resJson.put("punishedPersons", punishedPersonsSum);
                resJson.put("disciplinaryPunishedPersons", disciplinaryPunishedPersonsSum);
                resJson.put("recommendationLetters", recommendationLettersSum);
                resJson.put("circularCount", circularNoSum);
                resJson.put("circularIssues", circularIssuesSum);
                resList.add(resJson);
            }

            if (isExport != null && isExport) {
                ExcelWriter writer = ExcelUtil.getWriter(true);
                writer.addHeaderAlias("problemType", "突出问题类型数据统计");
                writer.addHeaderAlias("problemsFoundCount", "自查自纠发现问题数");
                writer.addHeaderAlias("inProgressCountSum", "整治处理中问题数");
                writer.addHeaderAlias("closedCount", "已整治销号总数");
                writer.addHeaderAlias("problemsFoundCountNew", "新增问题数");
                writer.addHeaderAlias("closedCountNew", "新增销号数");
                writer.addHeaderAlias("percent", "整改率");
                writer.addHeaderAlias("establishedSystems", "建立制度数");
                writer.addHeaderAlias("disciplinaryCases", "查处违纪违法案件数");
                writer.addHeaderAlias("punishedPersons", "组织处理人数");
                writer.addHeaderAlias("disciplinaryPunishedPersons", "纪律处分人数");
                writer.addHeaderAlias("recommendationLetters", "发出执纪执法建议（文书）数");
                writer.addHeaderAlias("circularCount", "印发通报期数");
                writer.addHeaderAlias("circularIssues", "通报涉及问题数");

                writer.write(resList, true);
                for (int i = 0; i < 7; i++) {
                    writer.setColumnWidth(i, 30);
                }
                for (int i = 0; i < list.size(); i++) {
                    writer.setColumnWidth(i, 30);
                }

                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=dataByProblemType.xlsx");
                ServletOutputStream out = response.getOutputStream();

                writer.flush(out, true);
                writer.close();
                IoUtil.close(out);
                return null;
            } else {
                return WrapMapper.wrap(Wrapper.SUCCESS_CODE, Wrapper.SUCCESS_MESSAGE, resList);
            }
        } catch (Exception e) {
            log.error("导出数据报错", e);
            return WrapMapper.wrap(Wrapper.ERROR_CODE, e.getMessage());
        }
    }

    @Override
    public void exportDataByEstablished(HttpServletResponse response, JSONObject reqJson) {
        try {
            NumberFormat nt = NumberFormat.getPercentInstance();
            nt.setMinimumFractionDigits(2);
            List<JyProblemManagement> list = jyDataMapper.queryProblemManagementPage(reqJson);
            List<JSONObject> resList = CollectionUtil.newArrayList();

            ExcelWriter writer = ExcelUtil.getWriter(true);
            writer.addHeaderAlias("department", "单位名称");
            writer.addHeaderAlias("establishedSystems", "建立制度数");
            writer.addHeaderAlias("establishedName", "制度清单");
            writer.addHeaderAlias("prominentProblem", "涉及清单查摆问题表现");
            writer.addHeaderAlias("problemType", "涉及突出问题类型");

            for (int i = 0; i < list.size(); i++) {
                JyProblemManagement jyProblemManagement = list.get(i);
                JSONObject resJson = new JSONObject();
                Integer mergeSize = 0;
                String department = jyProblemManagement.getDepartment();
                String prominentProblem = jyProblemManagement.getProminentProblem();
                String problemType = jyProblemManagement.getProblemType();
                Integer establishedSystems = jyProblemManagement.getEstablishedSystems();
                String establishedName = jyProblemManagement.getEstablishedName();

                List<String> establishedNames = CollectionUtil.newArrayList();
                if (establishedName != null) {
                    JSONArray establishedNameList = JSONArray.parseArray(establishedName);
                    for (int i1 = 0; i1 < establishedNameList.size(); i1++) {
                        JSONObject establishedNameJson = establishedNameList.getJSONObject(i1);
                        String name = establishedNameJson.getString("name");
                        if (StrUtil.isNotBlank(name)) {
                            mergeSize++;
                            establishedNames.add(name);
                        }
                    }
                }

                resJson.put("prominentProblem", prominentProblem);
                resJson.put("problemType", problemType);
                resJson.put("department", department);
                resJson.put("establishedSystems", establishedSystems);
                resJson.put("establishedName", null);
                resList.add(resJson);

                for (int j = 0; j < mergeSize; j++) {
                    JSONObject resJsonNew = new JSONObject();
                    resJsonNew.put("prominentProblem", null);
                    resJsonNew.put("problemType", null);
                    resJsonNew.put("department", null);
                    if (establishedNames.size() == 1) {
                        String establishedNameNew = establishedNames.get(0);
                        resJson.put("establishedName", establishedNameNew);
                    } else if (establishedNames.size() > 1) {
                        if (j == 0) {
                            String establishedNameNew = establishedNames.get(0);
                            resJson.put("establishedName", establishedNameNew);
                        } else if (establishedNames.size() > j) {
                            String establishedNameNew = establishedNames.get(j);
                            resJsonNew.put("establishedName", establishedNameNew);
                        }
                    }
                    if (StrUtil.isNotBlank(resJsonNew.getString("establishedName"))) {
                        resList.add(resJsonNew);
                    }
                }
            }
            writer.write(resList, true);
            for (int a = 0; a < 5; a++) {
                //设置宽度30
                writer.setColumnWidth(a, 30);
                if (a == 2) {
                    continue;
                }
                int back = 1;
                for (int b = 0; b < resList.size() + 1; b++) {
                    writer.setRowHeight(b, 20);
                    Cell cell = writer.getCell(a, b);
                    if (cellValueIsNUll(cell)) {
                        back++;
                    } else {
                        if (b > 1 && back > 1 && b - back > 0) {
                            if (!checkMergedRegionOverlap(writer, b - back, b, a, a)) {
//                                System.out.println(writer.getCell(a, b - back));
                                Cell cell1 = writer.getCell(a, b - back);
                                if (cell1 != null) {
                                    // 根据单元格类型获取值
                                    CellType cellType = cell1.getCellType();
                                    String cellValue = null;
                                    switch (cellType) {
                                        case STRING:
                                            cellValue = cell1.getStringCellValue();
                                            writer.merge(b - back, b - 1, a, a, cellValue, false);
                                            break;
                                        case NUMERIC:
                                            cellValue = String.valueOf(cell1.getNumericCellValue());
                                            writer.merge(b - back, b - 1, a, a, cellValue, false);
                                            break;
                                    }
                                }
                            }
                            back = 1;
                        }
                    }
                }
                if (back > 1) {
                    int b = resList.size() + 1;
                    if (!checkMergedRegionOverlap(writer, b - back, b, a, a)) {
//                        System.out.println(writer.getCell(a, b - back));
                        Cell cell1 = writer.getCell(a, b - back);
                        if (cell1 != null) {
                            // 根据单元格类型获取值
                            CellType cellType = cell1.getCellType();
                            String cellValue = null;
                            switch (cellType) {
                                case STRING:
                                    cellValue = cell1.getStringCellValue();
                                    writer.merge(b - back, b - 1, a, a, cellValue, false);
                                    break;
                                case NUMERIC:
                                    cellValue = String.valueOf(cell1.getNumericCellValue());
                                    writer.merge(b - back, b - 1, a, a, cellValue, false);
                                    break;
                            }
                        }
                    }
                }
            }

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=dataByEstablished.xlsx");
            ServletOutputStream out = response.getOutputStream();

            writer.flush(out, true);
            writer.close();
            IoUtil.close(out);
        } catch (Exception e) {
            log.error("导出数据报错", e);
        }
    }

    @Override
    public Wrapper<Object> dataByIsTopTen(HttpServletResponse response, JSONObject reqJson) {
        try {
            NumberFormat nt = NumberFormat.getPercentInstance();
            nt.setMinimumFractionDigits(2);
            reqJson.put("isTopTen", "1");
            Boolean isExport = reqJson.getBoolean("isExport");
            List<JyProblemManagement> list = jyDataMapper.queryProblemManagementPage(reqJson);
            List<JSONObject> resList = CollectionUtil.newArrayList();

            List<String> prominentProblemList = list.stream().map(JyProblemManagement::getProminentProblem).distinct().collect(Collectors.toList());
            for (String prominentProblem : prominentProblemList) {
                if (StrUtil.isBlank(prominentProblem)) {
                    continue;
                }
                JSONObject resJson = new JSONObject();
                List<JyProblemManagement> listByProminentProblem = list.stream().filter(JyProblemManagement -> prominentProblem.equals(JyProblemManagement.getProminentProblem())).collect(Collectors.toList());
                int problemsFoundCountSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getProblemsFoundCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int inProgressCountSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getInProgressCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int closedCountSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getClosedCount)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();

                int problemsFoundCountSumNew = 0;
                int closedCountSumNew = 0;
                for (JyProblemManagement jyProblemManagement : listByProminentProblem) {
                    int problemsFoundCountNew = 0;
                    int closedCountNew = 0;
                    Long id = jyProblemManagement.getId();
                    JyProblemManagementRecord jyProblemManagementRecord = new JyProblemManagementRecord();
                    jyProblemManagementRecord.setManagementId(id);
                    List<JyProblemManagementRecord> jyProblemManagementRecordList = jyProblemManagementRecordMapper.select(jyProblemManagementRecord);
                    if (CollectionUtil.isNotEmpty(jyProblemManagementRecordList)) {
                        if (jyProblemManagementRecordList.size() == 1) {
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(0);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagement.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagement.getClosedCount();
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        } else {
                            jyProblemManagementRecordList.sort(Comparator.comparing(JyProblemManagementRecord::getCreatedTime));
                            JyProblemManagementRecord jyProblemManagementRecordOld = jyProblemManagementRecordList.get(0);
                            JyProblemManagementRecord jyProblemManagementRecordNew = jyProblemManagementRecordList.get(jyProblemManagementRecordList.size() - 1);
                            Integer problemsFoundCountRecord = jyProblemManagementRecordNew.getProblemsFoundCount();
                            Integer closedCountRecord = jyProblemManagementRecordNew.getClosedCount();
                            Integer problemsFoundCountOld = jyProblemManagementRecordOld.getProblemsFoundCount();
                            Integer closedCountOld = jyProblemManagementRecordOld.getClosedCount();
                            problemsFoundCountNew = problemsFoundCountRecord - problemsFoundCountOld;
                            closedCountNew = closedCountRecord - closedCountOld;
                        }
                        problemsFoundCountSumNew = problemsFoundCountSumNew + problemsFoundCountNew;
                        closedCountSumNew = closedCountSumNew + closedCountNew;
                    }
                }
                int establishedSystemsSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getEstablishedSystems)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryCasesSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getDisciplinaryCases)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int punishedPersonsSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int disciplinaryPunishedPersonsSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getDisciplinaryPunishedPersons)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int recommendationLettersSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getRecommendationLetters)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();
                int circularNoSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getCircularNo)
                        .filter(StrUtil::isNotBlank)
                        .mapToInt(circularNo -> {
                            try {
                                JSONArray jsonArray = JSONArray.parseArray(circularNo);
                                return jsonArray.size();
                            } catch (Exception e) {
                                // 解析失败时返回 0
                                return 0;
                            }
                        })
                        .sum();
                int circularIssuesSum = listByProminentProblem.stream()
                        .map(JyProblemManagement::getCircularIssues)
                        .filter(Objects::nonNull)
                        .mapToInt(Integer::intValue)
                        .sum();

                resJson.put("prominentProblem", prominentProblem);
                resJson.put("problemsFoundCount", problemsFoundCountSum);
                resJson.put("closedCount", closedCountSum);
                resJson.put("inProgressCountSum", inProgressCountSum);
                resJson.put("problemsFoundCountNew", problemsFoundCountSumNew);
                resJson.put("closedCountNew", closedCountSumNew);
                resJson.put("percent", nt.format(problemsFoundCountSum == 0 ? 0.0d : (double) inProgressCountSum / problemsFoundCountSum));
                resJson.put("establishedSystems", establishedSystemsSum);
                resJson.put("disciplinaryCases", disciplinaryCasesSum);
                resJson.put("punishedPersons", punishedPersonsSum);
                resJson.put("disciplinaryPunishedPersons", disciplinaryPunishedPersonsSum);
                resJson.put("recommendationLetters", recommendationLettersSum);
                resJson.put("circularCount", circularNoSum);
                resJson.put("circularIssues", circularIssuesSum);
                resList.add(resJson);
            }

            if (isExport != null && isExport) {
                ExcelWriter writer = ExcelUtil.getWriter(true);
                writer.addHeaderAlias("prominentProblem", "十大攻坚问题类型");
                writer.addHeaderAlias("problemsFoundCount", "自查自纠发现问题数");
                writer.addHeaderAlias("inProgressCountSum", "整治处理中问题数");
                writer.addHeaderAlias("closedCount", "已整治销号总数");
                writer.addHeaderAlias("problemsFoundCountNew", "新增问题数");
                writer.addHeaderAlias("closedCountNew", "新增销号数");
                writer.addHeaderAlias("percent", "整改率");
                writer.addHeaderAlias("establishedSystems", "建立制度数");
                writer.addHeaderAlias("disciplinaryCases", "查处违纪违法案件数");
                writer.addHeaderAlias("punishedPersons", "组织处理人数");
                writer.addHeaderAlias("disciplinaryPunishedPersons", "纪律处分人数");
                writer.addHeaderAlias("recommendationLetters", "发出执纪执法建议（文书）数");
                writer.addHeaderAlias("circularCount", "印发通报期数");
                writer.addHeaderAlias("circularIssues", "通报涉及问题数");

                writer.write(resList, true);
                for (int i = 0; i < 7; i++) {
                    writer.setColumnWidth(i, 30);
                }
                for (int i = 0; i < list.size(); i++) {
                    writer.setColumnWidth(i, 30);
                }

                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=dataByIsTopTen.xlsx");
                ServletOutputStream out = response.getOutputStream();

                writer.flush(out, true);
                writer.close();
                IoUtil.close(out);
                return null;
            } else {
                return WrapMapper.wrap(Wrapper.SUCCESS_CODE, Wrapper.SUCCESS_MESSAGE, resList);
            }
        } catch (Exception e) {
            log.error("导出数据报错", e);
            return WrapMapper.wrap(Wrapper.ERROR_CODE, e.getMessage());
        }
    }

    /**
     * 检查新的合并区域是否与现有的合并区域重叠
     *
     * @param writer   ExcelWriter 对象
     * @param firstRow 新合并区域的起始行
     * @param lastRow  新合并区域的结束行
     * @param firstCol 新合并区域的起始列
     * @param lastCol  新合并区域的结束列
     * @return 如果重叠返回 true，否则返回 false
     */
    private boolean checkMergedRegionOverlap(ExcelWriter writer, int firstRow, int lastRow, int firstCol, int lastCol) {
        int numMergedRegions = writer.getSheet().getNumMergedRegions();
        for (int i = 0; i < numMergedRegions; i++) {
            org.apache.poi.ss.util.CellRangeAddress mergedRegion = writer.getSheet().getMergedRegion(i);
            int existingFirstRow = mergedRegion.getFirstRow();
            int existingLastRow = mergedRegion.getLastRow();
            int existingFirstCol = mergedRegion.getFirstColumn();
            int existingLastCol = mergedRegion.getLastColumn();

            boolean rowOverlap = (firstRow <= existingLastRow && lastRow >= existingFirstRow);
            boolean colOverlap = (firstCol <= existingLastCol && lastCol >= existingFirstCol);

            if (rowOverlap && colOverlap) {
                return true;
            }
        }
        return false;
    }

    /**
     * 校验是否为空
     */
    private boolean cellValueIsNUll(Cell cell) {
        // 根据单元格类型获取值
        if (cell == null) {
            return true;
        }
        CellType cellType = cell.getCellType();
        String cellValue = "";
        switch (cellType) {
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                break;
        }
        if ("".equals(cellValue)) {
            return true;
        } else {
            return false;
        }
    }

}
